﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace YoungEnterpriseServices
{    
    [ServiceBehavior(InstanceContextMode = InstanceContextMode.PerCall)]
    //[ServiceBehavior(InstanceContextMode = InstanceContextMode.Single)]
    [DataContract]
    public class DatabaseService : IDatabaseService
    {
        string connectionString = "Data Source=WIN-L3P2K59S1ST;Initial Catalog=Gruppe2_juleprojekt;User ID=sa;Password=Cisco6";   
        SqlConnection con;
        SqlCommand cmd;

        public int OpretTeam(string standNr, string teamNavn, string ansvarligNavn, string ansvarligEmail, string skole, string undervisersNavn, string undervisersEmail)
        {
            con = new SqlConnection(connectionString);
            cmd = new SqlCommand();
            cmd.Connection = con; //SqlConnection-object is added to SqlCommand-object
            cmd.CommandType = CommandType.StoredProcedure; // declares that we're using a stored procedure

            cmd.CommandText = "OpretTeam"; //angiv  stored procedure

            cmd.Parameters.Clear(); //tøm parameterlisten

            int teamId;

            SqlParameter par = new SqlParameter("@teamNavn", SqlDbType.VarChar);
            par.Value = teamNavn;
            cmd.Parameters.Add(par);

            par = new SqlParameter("@standNr", SqlDbType.Int);
            par.Value = standNr;
            cmd.Parameters.Add(par);

            par = new SqlParameter("@ansvarligNavn", SqlDbType.VarChar);
            par.Value = ansvarligNavn;
            cmd.Parameters.Add(par);

            par = new SqlParameter("@ansvarligEmail", SqlDbType.VarChar);
            par.Value = ansvarligEmail;
            cmd.Parameters.Add(par);

            par = new SqlParameter("@skole", SqlDbType.VarChar);
            par.Value = skole;
            cmd.Parameters.Add(par);

            par = new SqlParameter("@undervisersNavn", SqlDbType.VarChar);
            par.Value = undervisersNavn;
            cmd.Parameters.Add(par);

            par = new SqlParameter("@undervisersEmail", SqlDbType.VarChar);
            par.Value = undervisersEmail;
            cmd.Parameters.Add(par);

            par = new SqlParameter("@teamId", SqlDbType.Int);
            par.Direction = ParameterDirection.ReturnValue;
            cmd.Parameters.Add(par);            

            try
            {
                con.Open(); // opens connections
                cmd.ExecuteReader(); //executes co
                teamId = (int)cmd.Parameters["@teamId"].Value;
                return teamId;                
            }
            catch (SqlException e)
            {
                return -1;
            }
            finally //if connections is open, close it
            {
                if (con.State == ConnectionState.Open)
                {
                    con.Close();
                }
            }        
        }

        public bool OpretBedømmelse(int teamId, int kategoriId, int point)
        {
            con = new SqlConnection(connectionString);
            cmd = new SqlCommand();
            cmd.Connection = con; //SqlConnection-object is added to SqlCommand-object
            cmd.CommandType = CommandType.StoredProcedure; // declares that we're using a stored procedure


            cmd.CommandText = "OpretBedømmelse"; //angiv  stored procedure

            cmd.Parameters.Clear(); //tøm parameterlisten            

            SqlParameter par = new SqlParameter("@teamId", SqlDbType.Int);
            par.Value = teamId;
            cmd.Parameters.Add(par);

            par = new SqlParameter("@kategoriId", SqlDbType.Int);
            par.Value = kategoriId;
            cmd.Parameters.Add(par);

            par = new SqlParameter("@point", SqlDbType.Int);
            par.Value = point;
            cmd.Parameters.Add(par);

            try
            {
                con.Open(); // opens connections
                cmd.ExecuteReader(); //executes co                
                return true;
            }
            catch (SqlException e)
            {
                return false;
            }
            finally //if connections is open, close it
            {
                if (con.State == ConnectionState.Open)
                {
                    con.Close();
                }
            }
        }

        public int OpretMesse(string navn, string sted, string dato)
        {
            con = new SqlConnection(connectionString);
            cmd = new SqlCommand();
            cmd.Connection = con; //SqlConnection-object is added to SqlCommand-object
            cmd.CommandType = CommandType.StoredProcedure; // declares that we're using a stored procedure


            cmd.CommandText = "OpretMesse"; //angiv  stored procedure

            cmd.Parameters.Clear(); //tøm parameterlisten            

            int messeId;

            SqlParameter par = new SqlParameter("@navn", SqlDbType.VarChar);
            par.Value = navn;
            cmd.Parameters.Add(par);

            par = new SqlParameter("@sted", SqlDbType.VarChar);
            par.Value = sted;
            cmd.Parameters.Add(par);

            par = new SqlParameter("@dato", SqlDbType.VarChar);
            par.Value = dato;
            cmd.Parameters.Add(par);

            par = new SqlParameter("@messeId", SqlDbType.Int);
            par.Direction = ParameterDirection.ReturnValue;
            cmd.Parameters.Add(par);            

            try
            {
                con.Open(); // opens connections
                cmd.ExecuteReader(); //executes co
                messeId = (int)cmd.Parameters["@messeId"].Value;
                return messeId; 
            }
            catch (SqlException e)
            {
                return -1;
            }
            finally //if connections is open, close it
            {
                if (con.State == ConnectionState.Open)
                {
                    con.Close();
                }
            }
        }

        public int OpretKategori(int konkurrenceId, string navn, int maxPoint)
        {
            con = new SqlConnection(connectionString);
            cmd = new SqlCommand();
            cmd.Connection = con; //SqlConnection-object is added to SqlCommand-object
            cmd.CommandType = CommandType.StoredProcedure; // declares that we're using a stored procedure            

            cmd.CommandText = "OpretKategori"; //angiv  stored procedure

            cmd.Parameters.Clear(); //tøm parameterlisten            

            int kategoriId;
            SqlParameter par = new SqlParameter("@konkurrenceId", SqlDbType.Int);
            par.Value = konkurrenceId;
            cmd.Parameters.Add(par);

            par = new SqlParameter("@navn", SqlDbType.VarChar);
            par.Value = navn;
            cmd.Parameters.Add(par);

            par = new SqlParameter("@maxPoint", SqlDbType.Int);
            par.Value = maxPoint;
            cmd.Parameters.Add(par);

            par = new SqlParameter("@kategoriId", SqlDbType.Int);
            par.Direction = ParameterDirection.ReturnValue;
            cmd.Parameters.Add(par);

            try
            {
                con.Open(); // opens connections
                cmd.ExecuteReader(); //executes co
                kategoriId = (int)cmd.Parameters["@kategoriId"].Value;
                return kategoriId;
            }
            catch (SqlException e)
            {
                return -1;
            }
            finally //if connections is open, close it
            {
                if (con.State == ConnectionState.Open)
                {
                    con.Close();
                }
            }
        }

        public int OpretKonkurrence(string navn, int messeId)
        {
            con = new SqlConnection(connectionString);
            cmd = new SqlCommand();
            cmd.Connection = con; //SqlConnection-object is added to SqlCommand-object
            cmd.CommandType = CommandType.StoredProcedure; // declares that we're using a stored procedure


            cmd.CommandText = "OpretKonkurrence"; //angiv  stored procedure

            cmd.Parameters.Clear(); //tøm parameterlisten            

            int konkurrenceId;

            SqlParameter par = new SqlParameter("@navn", SqlDbType.VarChar);
            par.Value = navn;
            cmd.Parameters.Add(par);

            par = new SqlParameter("@messeId", SqlDbType.Int);
            par.Value = messeId;
            cmd.Parameters.Add(par);

            par = new SqlParameter("@konkurrenceId", SqlDbType.Int);
            par.Direction = ParameterDirection.ReturnValue;
            cmd.Parameters.Add(par);

            try
            {
                con.Open(); // opens connections
                cmd.ExecuteReader(); //executes co
                konkurrenceId = (int)cmd.Parameters["@konkurrenceId"].Value;
                return konkurrenceId;
            }
            catch (SqlException e)
            {
                return -1;
            }
            finally //if connections is open, close it
            {
                if (con.State == ConnectionState.Open)
                {
                    con.Close();
                }
            }
        }

        public int FindMesse(string MesseNavn)
        {
            SqlDataReader datareader = null;

            con = new SqlConnection(connectionString);
            cmd = new SqlCommand();
            cmd.Connection = con; //SqlConnection-object is added to SqlCommand-object
            cmd.CommandType = CommandType.StoredProcedure; // declares that we're using a stored procedure
            
            cmd.CommandText = "FindMesse"; //angiv  stored procedure

            cmd.Parameters.Clear(); //tøm parameterlisten       

            int MesseID = -1;

            SqlParameter par = new SqlParameter("@MesseNavn", SqlDbType.VarChar);
            par.Value = MesseNavn;
            cmd.Parameters.Add(par);

            try
            {
                con.Open(); // opens connections
                datareader = cmd.ExecuteReader(); //executes co
                while (datareader.Read() == true)
                {
                    MesseID = (int)datareader["MesseID"];
                }
                //MesseID = (int)cmd.Parameters["MesseID"].Value;
                
                return MesseID;
            }
            catch (SqlException e)
            {
                return -1;
            }
            finally //if connections is open, close it
            {
                if (datareader != null)
                {
                    datareader.Close();
                }
                if (con.State == ConnectionState.Open)
                {
                    con.Close();
                }
            }
            
        }

        public bool TilknytKonkurrenceTilTeam(int teamid, int konkurrenceId)
        {
            con = new SqlConnection(connectionString);
            cmd = new SqlCommand();
            cmd.Connection = con; //SqlConnection-object is added to SqlCommand-object
            cmd.CommandType = CommandType.StoredProcedure; // declares that we're using a stored procedure


            cmd.CommandText = "TilknytKonkurrence"; //angiv  stored procedure

            cmd.Parameters.Clear(); //tøm parameterlisten       

            SqlParameter par = new SqlParameter("@teamId", SqlDbType.Int);
            par.Value = teamid;
            cmd.Parameters.Add(par);

            par = new SqlParameter("@konkurrenceId", SqlDbType.Int);
            par.Value = konkurrenceId;
            cmd.Parameters.Add(par);


            try
            {
                con.Open(); // opens connections
                cmd.ExecuteReader(); //executes co
                return true;
            }
            catch (SqlException e)
            {
                return false;
            }
            finally //if connections is open, close it
            {
                if (con.State == ConnectionState.Open)
                {
                    con.Close();
                }
            }
        }

        public bool SletTeam(int teamId)
        {
            con = new SqlConnection(connectionString);
            cmd = new SqlCommand();
            cmd.Connection = con; //SqlConnection-object is added to SqlCommand-object
            cmd.CommandType = CommandType.StoredProcedure; // declares that we're using a stored procedure


            cmd.CommandText = "SletTeam"; //angiv  stored procedure

            cmd.Parameters.Clear(); //tøm parameterlisten       

            SqlParameter par = new SqlParameter("@teamId", SqlDbType.Int);
            par.Value = teamId;
            cmd.Parameters.Add(par);


            try
            {
                con.Open(); // opens connections
                cmd.ExecuteReader(); //executes co
                return true;
            }
            catch (SqlException e)
            {
                return false;
            }
            finally //if connections is open, close it
            {
                if (con.State == ConnectionState.Open)
                {
                    con.Close();
                }
            }
        }

        public bool SletKonkurrence(int konkurrenceId)
        {
            con = new SqlConnection(connectionString);
            cmd = new SqlCommand();
            cmd.Connection = con; //SqlConnection-object is added to SqlCommand-object
            cmd.CommandType = CommandType.StoredProcedure; // declares that we're using a stored procedure


            cmd.CommandText = "SletKonkurrence"; //angiv  stored procedure

            cmd.Parameters.Clear(); //tøm parameterlisten       

            SqlParameter par = new SqlParameter("@konkurrenceId", SqlDbType.Int);
            par.Value = konkurrenceId;
            cmd.Parameters.Add(par);

            try
            {
                con.Open(); // opens connections
                cmd.ExecuteReader(); //executes co
                return true;
            }
            catch (SqlException e)
            {
                return false;
            }
            finally //if connections is open, close it
            {
                if (con.State == ConnectionState.Open)
                {
                    con.Close();
                }
            }
        }

        public bool SletMesse(int messeId)
        {
            con = new SqlConnection(connectionString);
            cmd = new SqlCommand();
            cmd.Connection = con; //SqlConnection-object is added to SqlCommand-object
            cmd.CommandType = CommandType.StoredProcedure; // declares that we're using a stored procedure


            cmd.CommandText = "SletMesse"; //angiv  stored procedure

            cmd.Parameters.Clear(); //tøm parameterlisten       

            SqlParameter par = new SqlParameter("@messeId", SqlDbType.Int);
            par.Value = messeId;
            cmd.Parameters.Add(par);

            try
            {
                con.Open(); // opens connections
                cmd.ExecuteReader(); //executes co
                return true;
            }
            catch (SqlException e)
            {
                return false;
            }
            finally //if connections is open, close it
            {
                if (con.State == ConnectionState.Open)
                {
                    con.Close();
                }
            }
        }

        public bool SletKategori(int kategoriId)
        {
            con = new SqlConnection(connectionString);
            cmd = new SqlCommand();
            cmd.Connection = con; //SqlConnection-object is added to SqlCommand-object
            cmd.CommandType = CommandType.StoredProcedure; // declares that we're using a stored procedure


            cmd.CommandText = "SletKategori"; //angiv  stored procedure

            cmd.Parameters.Clear(); //tøm parameterlisten       

            SqlParameter par = new SqlParameter("@kategoriId", SqlDbType.Int);
            par.Value = kategoriId;
            cmd.Parameters.Add(par);

            try
            {
                con.Open(); // opens connections
                cmd.ExecuteReader(); //executes co
                return true;
            }
            catch (SqlException e)
            {
                return false;
            }
            finally //if connections is open, close it
            {
                if (con.State == ConnectionState.Open)
                {
                    con.Close();
                }
            }
        }

        public bool SletAlleKategorier()
        {
            con = new SqlConnection(connectionString);
            cmd = new SqlCommand();
            cmd.Connection = con; //SqlConnection-object is added to SqlCommand-object
            cmd.CommandType = CommandType.StoredProcedure; // declares that we're using a stored procedure

            cmd.CommandText = "SletAlleKategorier"; //angiv  stored procedure

            try
            {
                con.Open(); // opens connections
                cmd.ExecuteReader(); //executes co
                return true;
            }
            catch (SqlException e)
            {
                return false;
            }
            finally //if connections is open, close it
            {
                if (con.State == ConnectionState.Open)
                {
                    con.Close();
                }
            }
        }

        //public bool SletAlleKategorier()
        //{
        //    con = new SqlConnection(connectionString);
        //    cmd = new SqlCommand();
        //    cmd.Connection = con; //SqlConnection-object is added to SqlCommand-object
        //    cmd.CommandType = CommandType.StoredProcedure; // declares that we're using a stored procedure

        //    cmd.CommandText = "SletAlleKategorier"; //angiv  stored procedure

        //    try
        //    {
        //        con.Open(); // opens connections
        //        cmd.ExecuteReader(); //executes co
        //        return true;
        //    }
        //    catch (SqlException e)
        //    {
        //        return false;
        //    }
        //    finally //if connections is open, close it
        //    {
        //        if (con.State == ConnectionState.Open)
        //        {
        //            con.Close();
        //        }
        //    }
        //}
    }
}
